using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Text;
using MySql.Data.Common;
using MySql.Data.MySqlClient;
using myconn = MySql.Data.MySqlClient.MySqlConnection;
using SharpSoft.Data.GSQL;

namespace SharpSoft.Data
{
    using System.ComponentModel;
    [DisplayName("Mysql数据库")]
    public class MySql : Database
    {
        public MySql(string p_connstr) : base(MySqlClientFactory.Instance, p_connstr)
        {
        }


        public static string CreateConnectionString(string server, int port, string defdb, string uid, string pwd)
        {
            return $"Server={server};Port={(port <= 0 ? 3306 : port)};Database={defdb};Uid={uid};Pwd={pwd};";
        }

        public override void Dispose()
        {
            if (this.ClearPoolWhenDispose)
            {
                myconn.ClearPool((MySqlConnection)this.conn);
            }
            base.Dispose();
        }

        public override SQLTextGenerator SQLTextGenerator()
        {
            return new MySqlTextGenerator();
        }

        public static Database CreateSchema(string defaultConnStr, string schemaName)
        {
            MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder(defaultConnStr);
            var sql = $"CREATE DATABASE {schemaName};";
            MySqlConnection connection = new MySqlConnection(defaultConnStr);
            connection.Open();
            MySqlCommand command = new MySqlCommand(sql, connection);
            command.ExecuteNonQuery();
            builder.Database = schemaName;
            return new MsSql(builder.ToString());
        }
        /// <summary>
        /// 使用Mysql原生语法进行分页查询
        /// </summary>
        /// <typeparam name="TModel">单行数据的模型</typeparam>
        /// <param name="select">查询所有数据的select语句</param>
        /// <param name="onepagerowscount">一页数据的数量</param>
        /// <param name="curpageindex">要查询数据的页索引（页索引从0开始）</param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public override SqlPagination<TModel> ExecutePagination<TModel>(NativeSQLCommandScript select, int onepagerowscount, int curpageindex, object paras = null)
        {
            if (onepagerowscount < 1)
            {
                throw new Exception("分页查询时，一页的数量不能小于1.");
            }
            if (curpageindex < 0)
            {
                throw new Exception("分页查询时当前页索引不能小于0.");
            }
            if (!string.IsNullOrWhiteSpace(select.CommandText))
            {
                select.CommandText = select.CommandText.Trim().TrimEnd(';');//使用原生语句时需要去掉子查询中的分号，不然会有语法错误 
            }
            if (string.IsNullOrWhiteSpace(select.CommandText))
            {
                throw new Exception("分页查询指定的查询语句不能为空");
            }
            var sqlcount = (NativeSQLCommandScript)$"SELECT COUNT(*) FROM ({select.CommandText}) AS table1;";
            var allcount = this.ExecuteInt32(sqlcount, paras);
            var pagecount = (int)Math.Ceiling((decimal)allcount / (decimal)onepagerowscount);
            int limitstart = curpageindex * onepagerowscount;

            string query = (NativeSQLCommandScript)$"SELECT * FROM ({select.CommandText}) AS table2 LIMIT {limitstart},{onepagerowscount}";
            var data = this.ExecuteArray<TModel>(query, paras);
            SqlPagination<TModel> pagi = new SqlPagination<TModel>() { CurrentPage = curpageindex, PageCount = pagecount, AllDataCount = allcount };
            pagi.PageData = data;
            return pagi;
        }
    }
}
